-- Dashboard
-- Project Status = "Active"
-- 1) Change to sort by project ID
-- 2) Only management, HQ, and field office project types should show in report
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER FUNCTION [dbo].[Project_DB1](@id  int,@reginid int ) 
RETURNS TABLE RETURN SELECT  * from project where 
 projectstatusid=(select ID from projectstatus where statusname='Active') and 
 projecttypeid IN (SELECT ID from [projecttype] where [projecttype]='Management' OR [projecttype]='CIPE Headquarters' OR [projecttype]='CIPE Field Office') 
	and (project.programofficer=ISNULL(@id,project.programofficer) or 
	project.regionaldirector=ISNULL(@id,project.programofficer) or 
	project.programassistant=ISNULL(@id,project.programofficer)) and 
	regionid=ISNULL(@reginid,regionid);
-- projecttypeid NOT IN (SELECT ID from [projecttype] where [projecttype]='Balance Sheet' OR [projecttype]='Timesheet') and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);

GO

-- Dashboard
-- Project Status = "Over"
-- 1) Change to sort by project ID
-- 2) Only management, HQ, and field office project types should show in report
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
     ALTER FUNCTION [dbo].[Project_DB2](@id  int,@reginid int ) RETURNS TABLE RETURN SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Over') and 
     /*projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner')*/ 
     projecttypeid IN (SELECT ID from [projecttype] where [projecttype]='Management' OR [projecttype]='CIPE Headquarters' OR [projecttype]='CIPE Field Office') 
     and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);   
GO

-- Dashboard
-- Projects "Over" within 30 days
-- 1) Change to sort by project ID
-- 2) Only management, HQ, and field office project types should show in report
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER FUNCTION [dbo].[Project_DB3](@id  int,@reginid int ) RETURNS TABLE RETURN SELECT project.* FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id WHERE 
    /*((Not (projecttype.projecttype)='partner')*/ 
    ((projecttype.projecttype='Management' OR projecttype.projecttype='CIPE Headquarters' OR projecttype.projecttype='CIPE Field Office') 
    AND ((projectstatus.statusname)='active') AND ((project.projectenddate)>=getDate() And (project.projectenddate)<=getDate()+30)) and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);   
GO

-- Dashboard
-- Partner Grant Status = "Active"
-- 1) Change to sort by project ID
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER FUNCTION [dbo].[Project_DB6](@id  int,@reginid int ) RETURNS TABLE RETURN SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);   
GO

-- Dashboard
-- Partner Grant Status = "Over"
-- 1) Change to sort by project ID
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER FUNCTION [dbo].[Project_DB7](@id  int,@reginid int ) RETURNS TABLE RETURN SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Over') and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);   
GO

-- Dashboard
-- New Projects where no Payment Activity >= 60 days 
-- 2) Change to sort by project ID
-- 3) Only management, HQ, and field office project types should show in report
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER FUNCTION [dbo].[Project_DB4](@id  int,@reginid int ) RETURNS TABLE RETURN SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') and 
    /*projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner')*/ 
     projecttypeid IN (SELECT ID from [projecttype] where [projecttype]='Management' OR [projecttype]='CIPE Headquarters' OR [projecttype]='CIPE Field Office') 
    and DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id ) and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);   
GO

-- Dashboard
-- Active Projects with more than 90% Budget Disbursed
-- 1) Pulling wrong data. Currently showing that there are no projects when there are projects with more than 90% of budget disbursed
-- 2) Since there are no projects listed, make sure the sort is by project ID
-- 3) Only management, HQ, and field office project types should show in report
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
   ALTER FUNCTION [dbo].[Project_DB5](@id  int,@reginid int ) 
RETURNS TABLE RETURN 
select * from project where projectstatusid=(select ID from projectstatus where statusname='Active') 
and project.managementfunds!=0 
/*and projecttypeid NOT IN(select ID from [projecttype] where [projecttype]='Balance Sheet' OR [projecttype]='Timesheet' )*/ 
and projecttypeid IN (SELECT ID from [projecttype] where [projecttype]='Management' OR [projecttype]='CIPE Headquarters' OR [projecttype]='CIPE Field Office') 
/*and (select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9*/ 
and (ISNULL((SELECT SUM(paymentrequestamount) FROM paymentrequestlineitem AS payment INNER JOIN paymentrequest ON paymentrequest.id = payment.paymentrequestid 
WHERE (projectid = project.id) AND (paymentrequest.statuscode != 'Cancelled' AND paymentrequest.statuscode != 'Cancel Requested')), 0) + ISNULL((SELECT DISTINCT TOTAL 
FROM solomon_all AS solomon WHERE ([CIPE GRANT NUMBER] = project.projectid)), 0))/project.managementfunds>=0.9 
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);
GO

-- Dashboard
-- New Partner Grants where Signed Grant Agreement Not Received 
-- 1) Pulling wrong data. Was keeping active items and didn't list pending items. Right now we should have at least two. It should filter by the Receipt of Signed Agreement Date field
-- 2) Since there are no projects listed, make sure the sort is by project ID
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER FUNCTION [dbo].[Project_DB8](@id  int,@reginid int ) RETURNS TABLE RETURN SELECT project.* FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id WHERE (((projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active' OR (projectstatus.statusname)='pending') AND ((project.projectstartdate)<getDate()) AND ((project.receiptofsignedagreement) Is Null)) and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);   
GO

-- Dashboard
-- Active Partner Grants with more than 90% Budget Disbursed
-- 1) Pulling wrong data (Missing 0111603-000-0B and 0111606-000-0C)
-- 2) Since there are no projects listed, make sure the sort is by project ID
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER FUNCTION [dbo].[Project_DB10](@id  int,@reginid int ) RETURNS TABLE RETURN SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and project.managementfunds!=0 
    /*and (select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9*/ 
    and (ISNULL((SELECT SUM(paymentrequestamount) FROM paymentrequestlineitem AS payment INNER JOIN paymentrequest ON paymentrequest.id = payment.paymentrequestid 
    WHERE (projectid = project.id) AND (paymentrequest.statuscode != 'Cancelled' AND paymentrequest.statuscode != 'Cancel Requested')), 0))/project.managementfunds>=0.9 
    and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);   
GO